package com.apes.scm.voucher.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.contract.model.Contract;
import com.apes.scm.masterdata.party.model.PartyRole;
import com.apes.scm.voucher.model.Voucher;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface VoucherRepository extends CustomRepository<Voucher, String> {
    /**
     * fetch data by date and company id and type
     *
     * @param companyId     partner id
     * @param voucherType   voucherType
     * @param inventoryMode inventoryMode
     * @param startDate     start date
     * @param endDate       end date
     * @return List<Map>
     */
    @Query("select new map(T.id as id ,T.amtTotal as amtTotal ,T.partner.party.name as partnerName ,T.company.party.name as companyName," +
            " T.createDate as createDate ,f.priceTotal as priceTotal,f.supplyChainManagementFee as supplyChainManagementFee,f.platformManagementFee as platformManagementFee ,f.product.id as productId," +
            " f.product.name as productName ,f.productQty as productQty,f.priceUnit as priceUnit )" +
            " from Voucher T left join T.voucherItems f where T.createDate < ?5 and T.createDate >= ?4" +
            " and T.company.id =?1 and T.voucherType =?2 and T.inventoryMode =?3 and (T.scene.id <>'PO002' or T.scene is null ) and T.originBusinessType <>'ProfitAndLoss' and T.partner.id <>'A00000' and T.partner.id <>'004465' ")
    List<Map> findByCompanyAndVoucherType(String companyId, String voucherType, String inventoryMode, Date startDate, Date endDate);

    /**
     * fetch data by date and company id and type
     *
     * @param companyId     partner id
     * @param voucherType   voucherType
     * @param inventoryMode inventoryMode
     * @param endDate       end date
     * @return List<Map>
     */
    @Query("select new map(T.id as id ,T.amtTotal as amtTotal ,T.partner.party.name as partnerName ,T.company.party.name as companyName," +
            " T.createDate as createDate ,f.priceTotal as priceTotal,f.supplyChainManagementFee as supplyChainManagementFee,f.platformManagementFee as platformManagementFee ,f.product.id as productId," +
            " f.product.name as productName ,f.productQty as productQty,f.priceUnit as priceUnit )" +
            " from Voucher T left join T.voucherItems f where T.createDate < ?4 " +
            " and T.company.id =?1 and T.voucherType =?2 and T.inventoryMode =?3  and (T.scene.id <>'PO002' or T.scene is null ) and T.originBusinessType <>'ProfitAndLoss' and T.partner.id <>'A00000' and T.partner.id <>'004465' ")
    List<Map> findByCompanyAndVoucherTypeAndEndDate(String companyId, String voucherType, String inventoryMode, Date endDate);

    /**
     * fetch data by date and company id and type
     *
     * @param companyId     partner id
     * @param voucherType   voucherType
     * @param inventoryMode inventoryMode
     * @param startDate     start date
     * @param endDate       end date
     * @return List<Map>
     */
    @Query("select T from Voucher T where T.createDate < ?5 and T.createDate >= ?4" +
            " and T.company.id =?1 and T.voucherType =?2 and T.inventoryMode =?3  and (T.scene.id <>'PO002' or T.scene is null ) and T.originBusinessType <>'ProfitAndLoss'  and T.partner.id <>'004465' ")
    List<Voucher> findByCompanyAndVoucherTypeAndPartner(String companyId, String voucherType, String inventoryMode, Date startDate, Date endDate);


    /**
     * 根据源明细行找对应的销售凭证
     *
     * @param company      销售公司
     * @param partner      客户
     * @param originItemId 来源明细行号
     * @return Voucher
     */
    @Query("select t from Voucher t left join t.voucherItems f " +
            " where t.company =:company and t.partner =:partner and t.voucherType = 'CUSTOMER_SALE' and f.originItemId = :originItemId ")
    Voucher findSaleVoucherByCompanyAndItemOrigin(@Param("company") PartyRole company, @Param("partner") PartyRole partner, @Param("originItemId") String originItemId);


    @Query("select t from Voucher t left join t.voucherItems f " +
            " where t.company =:company and t.partner =:partner and t.voucherType = 'CUSTOMER_SALE' and f.sourceItemId = :sourceItemId ")
    Voucher findSaleVoucherByCompanyAndItemSource(@Param("company") PartyRole company, @Param("partner") PartyRole partner, @Param("sourceItemId") String sourceItemId);


    @Query("select t from Voucher t left join t.voucherItems f  " +
            "where t.company =:company and t.partner =:partner and t.voucherType = 'SUPPLIER_PURCHASE' " +
            "and t.origin = :origin and f.sourceItemId = :sourceItemId")
    Voucher findPurchaseVoucherByCompanyAndOrigin(@Param("company") PartyRole company, @Param("partner") PartyRole partner,
                                                  @Param("origin") String origin, @Param("sourceItemId") String sourceItemId);

    @Query("select t from Voucher t left join t.voucherItems f" +
            " where t.company =:company and t.partner =:partner and t.voucherType = 'SUPPLIER_PURCHASE' " +
            "  and t.origin = :origin and f.sourceItemId = :sourceItemId ")
    Voucher findPurchaseVoucherByCompanyAndOriginAndItemId(@Param("company") PartyRole company, @Param("partner") PartyRole partner,
                                                           @Param("origin") String origin, @Param("sourceItemId") String sourceItemId);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('采购订单', '零售单', '分销订单', '服务方案', '服务工单', '零售退回')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and a.create_date >= :createDate and a.create_date < :createDate+1" ,nativeQuery = true)
    List<Map> findStorageNumber(String productId,String inventory,String company,Date createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('采购订单', '零售单', '分销订单', '服务方案', '服务工单', '零售退回')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7)" ,nativeQuery = true)
    List<Map> findStorageMonth(String productId,String inventory,String company,String createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('返厂订单', '售后工单')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and a.create_date >= :createDate and a.create_date < :createDate+1" ,nativeQuery = true)
    List<Map> findPurchaseReturn(String productId,String inventory,String company,Date createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('返厂订单', '售后工单')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and  to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7)" ,nativeQuery = true)
    List<Map> findPurchaseReturnMonth(String productId,String inventory,String company,String createDate);


    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('损益单')" +
            "   and  a.voucher_type in('CUSTOMER_SALE','SUPPLIER_PURCHASE') and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and a.create_date >= :createDate and a.create_date < :createDate+1" ,nativeQuery = true)
    List<Map> findProfitAndLoss(String productId,String inventory,String company,Date createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('损益单')" +
            "   and a.voucher_type in('CUSTOMER_SALE','SUPPLIER_PURCHASE')  and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7)" ,nativeQuery = true)
    List<Map> findProfitAndLossMonth(String productId,String inventory,String company,String createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('跨公司调拨单')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and a.create_date >= :createDate and a.create_date < :createDate+1" ,nativeQuery = true)
    List<Map> findTran(String productId,String inventory,String company,Date createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "        b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id" +
            "   and C.origin_business_type in ('跨公司调拨单')" +
            "   and a.voucher_type='SUPPLIER_PURCHASE' and b.product_id = :productId and a.inventory_mode = :inventory" +
            "   and a.company_id =:company and to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7)" ,nativeQuery = true)
    List<Map> findTranMonth(String productId,String inventory,String company,String createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "       b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id  and C.origin_business_type not in ('损益单')" +
            "   and a.voucher_type='CUSTOMER_SALE' and b.product_id = :productId and a.inventory_mode = 'IM_OWN'" +
            "   and a.company_id =:company and a.create_date >= :createDate and a.create_date < :createDate+1" ,nativeQuery = true)
    List<Map> findSale(String productId,String company,Date createDate);

    @Query(value ="SELECT '[' || b.product_id || ']' ||" +
            "        (SELECT M.NAME FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) AS PRODUCT_NAME," +
            "       case  when a.Inventory_Mode = 'IM_CONSIGN' then" +
            "          '代销'  else '经销' end inventoryMode, b.PRODUCT_QTY, b.PRICE_UNIT," +
            "       b.price_subtotal amount," +
            "       to_char(a.create_date, 'yyyy-mm-dd') create_date," +
            "       '[' ||  (SELECT M.Classify_Id FROM MAS_PRODUCT M WHERE b.PRODUCT_ID = M.ID) || ']' ||" +
            "        (SELECT z.NAME FROM MAS_PRODUCT M, mas_classify z WHERE m.classify_id = z.id" +
            "            and b.PRODUCT_ID = M.ID) classify, a.ORIGIN," +
            "       c.origin_business_type, a.source, c.source_business_type," +
            "       C.CAR_LICENSE CAR_LICENSE" +
            "  FROM acc_voucher a, acc_voucher_item b" +
            "  LEFT JOIN ACC_VOUCHER_EXTENSION C" +
            "    ON B.ID = C.ID where a.id = b.voucher_id  and C.origin_business_type not in ('损益单')" +
            "   and a.voucher_type='CUSTOMER_SALE' and b.product_id = :productId and a.inventory_mode = 'IM_OWN'" +
            "   and a.company_id =:company and to_char(a.create_date,'yyyy-mm') = substr(:createDate,0,7)" ,nativeQuery = true)
    List<Map> findSaleMonth(String productId,String company,String createDate);
}
